import pymysql
import re
from pypinyin import pinyin, lazy_pinyin, Style


def adjustname():
    sql="select sysno,c_office_type_desc_chn from 官职表_new where c_office_name is null  "
    cur2.execute(sql)
    datas=cur2.fetchall()
    value=[]
    no_chinese = re.compile(u'[^\u4e00-\u9fa5]')  #检查非中文
    for data in datas:
        sysno=data[0]
        name=data[1].replace('\ufeff','')
        match=no_chinese.search(name)
        if match==None:
            pinyin=lazy_pinyin(name)
            pinyin_name=''
            for a in pinyin:
                pinyin_name=pinyin_name+a.capitalize()
            value.append((pinyin_name,sysno))
            if len(value)>10000:
                update="update `官职表_new` set c_office_name=%s where sysno=%s"
                cur2.executemany(update,value)
                conn2.commit()
                value=[]
        # else:
        #     name=name[:1]
        #     pinyin=lazy_pinyin(name)
        #     pinyin_name=''
        #     for a in pinyin:
        #         pinyin_name=pinyin_name+a.capitalize()
        #     value.append((pinyin_name,sysno))
        #     if len(value)>10000:
        #         update="update `官职表_new` set c_office_name=%s where sysno=%s"
        #         cur2.executemany(update,value)
        #         conn2.commit()
        #         value=[]
    update="update `官职表_new` set c_office_name=%s where sysno=%s"
    cur2.executemany(update,value)
    conn2.commit()

if __name__ == '__main__':
    conn2 = pymysql.Connect(host = '10.170.128.56',
                            port = 3306,
                            user = 'root',
                            passwd = '123456',
                            db = 'cnki_humantity_simple',
                            charset='utf8mb4')
    cur2 = conn2.cursor()
    adjustname()

    cur2.close()
    conn2.close()